package com.navi.util;

import com.alibaba.fastjson.JSONObject;
import com.navi.entity.ColumnEntity;
import com.navi.entity.TableEntity;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;

import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import static com.navi.constant.GenericDef.*;
import static com.navi.constant.GenericDef.ARYCF_PDS_RESULT_EQPT_T;
import static com.navi.constant.GenericDef.CELL1_PDS_RESULT_EQPT_T;

/**
 * Created by ivo on 2017/5/24.
 */
public class TableJdbcUtil {
    private final static Logger logger = LoggerFactory.getLogger(TableJdbcUtil.class);

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String driver = "org.postgresql.Driver";
//        String url = "jdbc:postgresql://10.50.10.163:5432/qmstst";
//        String usr = "sys";
//        String passwd = "sysadmin";
        String url = "jdbc:postgresql://10.50.10.161:5432/qmsprd";
        String usr = "sys";
        String passwd = "qmsadmin";
        final Connection connection = getConnection(driver, url, usr, passwd);
        final Statement stmt = connection.createStatement();
        final String stageTableSchema = "stage";
        final String stageTableName = "templates11_wpp_fdefect_f";
        final ResultSet rs = stmt.executeQuery("SELECT min(evt_Timestamp) mint ,max(evt_Timestamp) maxt," +
                "ARRAY_TO_STRING(ARRAY_AGG(DISTINCT ope_id),',') as opes FROM " + stageTableSchema + "." + stageTableName);


        while (rs.next()) {
            final String mint = rs.getString("mint");
            final String maxt = rs.getString("maxt");
            final String opes = rs.getString("opes");
            final String opeCondition = Arrays.stream(opes.split(","))
                    .map(o -> "'" + o + "'").collect(Collectors.joining(","));

            System.out.println(mint);
            System.out.println(maxt);
            System.out.println(opeCondition);
        }
//        getTableColumn(connection, tableName);
    }

    public static Connection getConnection(String driver, String url, String usr, String passwd) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, usr, passwd);
        } catch (ClassNotFoundException e) {
            logger.error("加载驱动异常");
            throw e;
        } catch (SQLException e) {
            logger.error("连接数据库失败");
            throw e;
        }
        //catch(ClassNotFoundException | SQLException e)
        return connection;

    }

    public static List<ColumnEntity> getTableColumn(Connection connection, String tableName) {

        List<ColumnEntity> columnEntityList = new ArrayList<>();

        String sql = "SELECT column_name,data_type from INFORMATION_SCHEMA.COLUMNS where table_name = '" + tableName + "' order by ordinal_position";


        try {
            Statement statement = null;
            ResultSet rs = null;
            try {
                statement = connection.createStatement();
                rs = statement.executeQuery(sql);
                while (rs.next()) {
                    columnEntityList.add(new ColumnEntity(rs.getString("column_name"), rs.getString("data_type")));
                }
            } catch (SQLException e) {
                throw e;
            } finally {
                if(null!=statement) {
                    statement.close();
                }
                if (null != rs) {
                    rs.close();
                }
            }
        } catch (SQLException e) {
            logger.error("获取表结构时出错。" + e.toString());
            e.printStackTrace();
        }
        return columnEntityList;
    }


    public static TableEntity getTableMetaInfo(String nspName, String tableName, Statement stmt) throws SQLException {
        /*** 2018/9/21 增加特殊逻辑。因为是用约束判断主键。sor defect子分区表结构主键约束与主表不一致。这样会导致主键错误。所以增加此逻辑 **/
        String mainTableName = tableName;
        if (TABLE_SCHEMA_EDA.equals(nspName)) {
            //PDS RESULT
            if (Arrays.asList(EDA_PDS_RESULT_TABLE_ARYCF.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = ARYCF_PDS_RESULT_EQPT_T;
            } else if (Arrays.asList(EDA_PDS_RESULT_TABLE_CELL1.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL1_PDS_RESULT_EQPT_T;
            } else if (Arrays.asList(EDA_PDS_RESULT_TABLE_CELL2.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL2_PDS_RESULT_EQPT_T;
            }
            //PDS SUMMARY
            if (Arrays.asList(EDA_PDS_GLASS_SUMMARY_TABLE_ARYCF.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = ARYCF_PDS_GLASS_SUMMARY_EQPT_T;
            } else if (Arrays.asList(EDA_PDS_GLASS_SUMMARY_TABLE_CELL1.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL1_PDS_COMPT_SUMMARY_EQPT_T;
            } else if (Arrays.asList(EDA_PDS_GLASS_SUMMARY_TABLE_CELL2.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL2_PDS_CHIP_SUMMARY_EQPT_T;
            }
            //PDS GLASS
            else if (Arrays.asList(EDA_PDS_GLASS_TABLE_ARYCF.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = ARYCF_PDS_GLASS_EQPT_T;
            } else if (Arrays.asList(EDA_PDS_GLASS_TABLE_CELL1.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL1_PDS_GLASS_EQPT_T;
            } else if (Arrays.asList(EDA_PDS_GLASS_TABLE_CELL2.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL2_PDS_GLASS_EQPT_T;
            } else //MDS RESULT
                if (Arrays.asList(EDA_MDS_RESULT_TABLE_ARYCF.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                    mainTableName = ARYCF_MDS_RESULT_EQPT_T;
                } else if (Arrays.asList(EDA_MDS_RESULT_TABLE_CELL1.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                    mainTableName = CELL1_MDS_RESULT_EQPT_T;
                } else if (Arrays.asList(EDA_MDS_RESULT_TABLE_CELL2.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                    mainTableName = CELL2_MDS_RESULT_EQPT_T;
                }
            //MDS SUMMARY
            if (Arrays.asList(EDA_MDS_GLASS_SUMMARY_TABLE_ARYCF.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = ARYCF_MDS_GLASS_SUMMARY_EQPT_T;
            } else if (Arrays.asList(EDA_MDS_GLASS_SUMMARY_TABLE_CELL1.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL1_MDS_COMPT_SUMMARY_EQPT_T;
            } else if (Arrays.asList(EDA_MDS_GLASS_SUMMARY_TABLE_CELL2.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL2_MDS_CHIP_SUMMARY_EQPT_T;
            }
            //MDS GLASS
            else if (Arrays.asList(EDA_MDS_GLASS_TABLE_ARYCF.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = ARYCF_MDS_GLASS_EQPT_T;
            } else if (Arrays.asList(EDA_MDS_GLASS_TABLE_CELL1.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL1_MDS_GLASS_EQPT_T;
            } else if (Arrays.asList(EDA_MDS_GLASS_TABLE_CELL2.split(",")).stream().filter(ele -> tableName.startsWith(ele)).count() > 0) {
                mainTableName = CELL2_MDS_GLASS_EQPT_T;
            }
        }

        TableEntity table = new TableEntity();
        List<ColumnEntity> columnEntityList = new ArrayList<>();
        StringBuffer queryBuffer = new StringBuffer("select nt.nspname as table_schema,");
        queryBuffer.append(" c.relname as table_name,");
        queryBuffer.append(" a.attname as column_name,");
        queryBuffer.append("       a.attnum as ordinal_position,");
        queryBuffer.append(" format_type(a.atttypid, a.atttypmod) as data_type,");
        queryBuffer.append(" c.relkind = 'r' AS is_updatable,");
        queryBuffer.append(" a.atttypid in (23, 20) and a.atthasdef and");
        queryBuffer.append(" (select position ( 'nextval(' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0 and");
        queryBuffer.append(" position ( '::regclass)' in pg_catalog.pg_get_expr(adbin,adrelid) ) > 0");
        //get attrelid for query primary key
        queryBuffer.append(" attrelid");
        queryBuffer.append(" FROM pg_catalog.pg_attrdef d");
        queryBuffer.append(" WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as has_sequence");
        queryBuffer.append(", attrelid");
        queryBuffer.append(" from pg_catalog.pg_class c join pg_catalog.pg_namespace nt on (c.relnamespace = nt.oid)");
        queryBuffer.append(" join pg_attribute a on (a.attrelid = c.oid)");
        queryBuffer.append(" where c.relname = '%s' and nt.nspname = '%s'");
        queryBuffer.append(" and a.attnum > 0 and a.attisdropped = 'f'");
        queryBuffer.append(" order by a.attnum");
        String queryString = String.format(queryBuffer.toString(), mainTableName, nspName);
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(queryString);
            String attrelid = null;
            while (rs.next()) {
                String tableSchema = rs.getString("table_schema");
                String columnName = rs.getString("column_name");
                Integer ordinalPosition = rs.getInt("ordinal_position");
                String dataType = rs.getString("data_type");
                if (StringUtils.equalsIgnoreCase(dataType, "bigserial")) {
                    dataType = "bigint";
                } else if (StringUtils.equalsIgnoreCase(dataType, "serial")) {
                    dataType = "int4";
                }
                String isUpdatatable = rs.getString("is_updatable");
                String hasSequence = rs.getString("has_sequence");
                if (!"db_timestamp".equalsIgnoreCase(columnName)) {
                    ColumnEntity column = new ColumnEntity(tableSchema, tableName, columnName, ordinalPosition, dataType, isUpdatatable, hasSequence, false);
                    columnEntityList.add(column);
                    attrelid = rs.getString("attrelid");
                }



            }

            if (StringUtils.isEmpty(attrelid)) {
                table.setHasPrimaryKey(false);
            } else {
                String sql = String.format("SELECT conkey FROM   pg_CONSTRAINT  a WHERE conrelid = '%s' and conkey is not null", attrelid);
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    table.setHasPrimaryKey(true);
                    final Array primaryKeyArr = rs.getArray("conkey");
                    final Integer[] keyPositions = (Integer[]) primaryKeyArr.getArray();
                    columnEntityList = columnEntityList.stream().map(columnEntity -> {

                        final boolean isMatch = Stream.of(keyPositions)
                                .anyMatch(keyPosition -> keyPosition.equals(columnEntity.getOrdinalPosition()));

                        columnEntity.setPrimaryKeyFlg(isMatch);
                        return columnEntity;
                    }).collect(Collectors.toList());
                }
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (null != rs) {
                rs.close();
            }
        }
        table.setTableName(tableName);
        table.setColumnEntityList(columnEntityList);
        return table;

    }


    public static String getTableNspName(String tableName, Statement stmt) throws SQLException {
        String sql = "SELECT n.nspname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = '"
                + tableName + "'";
//                + "' AND pg_catalog.pg_table_is_visible(c.oid)";
        ResultSet rs = stmt.executeQuery(sql);
        try {
            while (rs.next()) {
                return rs.getString(1);
            }
            return null;
        } catch (SQLException e) {
            throw e;
        } finally {
            rs.close();
        }
    }

    /**
     * 列名拼接的字符串，逗号分隔，带表别名
     *
     * @param columnEntityList
     * @param tableAlias
     * @return
     */
    public static String getColumnStringWithTableAlias(List<ColumnEntity> columnEntityList, String tableAlias) {
        return columnEntityList.stream()
                .map(ColumnEntity::getColumnName)
                .map(columnName -> String.format("%s.%s", tableAlias, columnName))
                .collect(Collectors.joining(","));
    }

    /**
     * 列名拼接的字符串，逗号分隔
     *
     * @param columnEntityList
     * @return
     */
    public static String getColumnString(List<ColumnEntity> columnEntityList) {
        return columnEntityList.stream().map(ColumnEntity::getColumnName).collect(Collectors.joining(","));
    }

    /**
     * 获取列vs值 MAP
     *
     * @param j
     * @param columnList
     * @param colCameNameMap
     * @return
     */
    public static Map<String, String> getColumnMap(final JSONObject j, final List<String> columnList, final Map<String, String> colCameNameMap) {
        Map<String, String> colMap = new HashMap<>();
        columnList.forEach(c -> {
            String value = j.getString(c);
            if (null == value && null != colCameNameMap.get(c)) {
                value = j.getString(colCameNameMap.get(c));
            }
            colMap.put(c, value);
        });
        return colMap;
    }

    /**
     * 拼接列值
     *
     * @param j
     * @param columnList
     * @param colCameNameMap
     * @return
     */
    public static String getColumnValString(final JSONObject j, final List<String> columnList, final Map<String, String> colCameNameMap) {
        String vals = columnList.stream().map(c -> {
            String value = j.getString(c);
            if (null == value && null != colCameNameMap.get(c)) {
                value = j.getString(colCameNameMap.get(c));
            }
            return value;
        }).collect(Collectors.joining("-"));

        vals = StringUtils.isEmpty(vals) ? "" : vals;
        return vals.replace(" ", "_");
    }

    /**
     * 主键名list
     *
     * @param tableEntity
     * @return
     */
    public static List<String> getPrimaryColumnList(TableEntity tableEntity) {
        return tableEntity.getColumnEntityList()
                .stream().filter(ColumnEntity::getPrimaryKeyFlg)
                .map(ColumnEntity::getColumnName).collect(Collectors.toList());
    }

    /**
     * 下划线转 驼峰风格
     *
     * @param line
     * @return
     */
    public static String underline2Camel(String line) {
        return underline2Camel(line, true);
    }

    /**
     * 下划线转 驼峰风格
     *
     * @param line
     * @param smallCamel
     * @return
     */
    public static String underline2Camel(String line, boolean smallCamel) {
        if (line == null || "".equals(line)) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        Pattern pattern = Pattern.compile(PATTERN_UNDERLINE2CAMEL);
        Matcher matcher = pattern.matcher(line);
        while (matcher.find()) {
            String word = matcher.group();
            sb.append(smallCamel && matcher.start() == 0 ? Character.toLowerCase(word.charAt(0)) : Character.toUpperCase(word.charAt(0)));
            int index = word.lastIndexOf('_');
            if (index > 0) {
                sb.append(word.substring(1, index).toLowerCase());
            } else {
                sb.append(word.substring(1).toLowerCase());
            }
        }
        return sb.toString();
    }


    /**
     * 主键拼接的 on 字符串
     * into_table.glass_id = from_table.glass_id and into_table.evt_timestamp = from_table.evt_timestamp and into_table....
     *
     * @param primaryColumnList
     * @param fromTableAlias
     * @param toTableAlias
     * @return
     */
    public static String getOnConditionStringByPk(List<ColumnEntity> primaryColumnList, String fromTableAlias, String toTableAlias) {
        return primaryColumnList.stream()
                .map(ColumnEntity::getColumnName)
                .map(columnName -> String.format(" %s.%s = %s.%s ", fromTableAlias, columnName, toTableAlias, columnName))
                .collect(Collectors.joining(" and "));
    }

    /**
     * build insert sql
     * <p>
     * insert into sor.wpp_fdefect_f (...)    -- columnString
     * (   select
     * from_table.glass_id, from_table.evt_timestamp, from_table....
     * from stage.templates11_wpp_fdefect_f as from_table
     * left outer join sor.wpp_fdefect_f into_table
     * on into_table.glass_id = from_table.glass_id and into_table.evt_timestamp = from_table.evt_timestamp and into_table....    -- primaryColumnList
     * and into_table.EVT_TIMESTAMP BETWEEN '2018-04-14 15:46:22' AND '2018-04-30 15:44:23'                                       -- additionCondition
     * and into_table.ope_id in ('C1311','C1341','C1351','C1353','C1550','C1560','C156J','C1590','C159I')                         -- additionCondition
     * where
     * into_table.glass_id is null
     * );
     *
     * @param fromTableSchema
     * @param fromTableName
     * @param intoTableSchema
     * @param intoTableName
     * @param fromTableAlias
     * @param toTableAlias
     * @param columnString
     * @param primaryColumnList
     * @param tableEntity
     * @param additionCondition into_table.EVT_TIMESTAMP & into_table.ope_id 限制条件
     * @return
     */
    public static String buildInsertNotExistedSql(String fromTableSchema, String fromTableName,
                                                  String intoTableSchema, String intoTableName,
                                                  String fromTableAlias, String toTableAlias,
                                                  String columnString, List<ColumnEntity> primaryColumnList, TableEntity tableEntity,
                                                  String additionCondition) {
        final String fromColumnString = TableJdbcUtil.getColumnStringWithTableAlias(tableEntity.getColumnEntityList(), fromTableAlias);
        final String onConditionString = TableJdbcUtil.getOnConditionStringByPk(primaryColumnList, fromTableAlias, toTableAlias);

        StringBuilder sqlInsert = new StringBuilder("insert into ")
                .append(intoTableSchema).append(".").append(intoTableName).append("(").append(columnString).append(")")
                .append("( select ").append(fromColumnString).append(" from ").append(fromTableSchema).append(".").append(fromTableName).append(" as from_table ");
        sqlInsert.append(" left outer join ").append(intoTableSchema).append(".").append(intoTableName).append(" into_table ")
                .append(" on ").append(onConditionString);
        if (StringUtils.isNotEmpty(additionCondition)) {
            sqlInsert.append(additionCondition);
        }
        sqlInsert.append(" where into_table.").append(primaryColumnList.get(0).getColumnName()).append(" is null ");

        sqlInsert.append(" ) ");

        return sqlInsert.toString();
    }
}
